/*********************************************************************************
 *      Copyrightt:  (C) 2025 LingYun<lingyun@email.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(15/03/25)
 *         Author:  LingYun <lingyun@email.com>
 *      ChangeLog:  1, Release initial version on "15/03/25 17:06:13"
 *                 
 ********************************************************************************/

#include "database.h"

static sqlite3         *s_serdb = NULL;


static int callback(void *notused, int argc, char **argv, char **azcolname)
{
	(void)notused;
	int i;

	if(argc > 0 && (argv == NULL || azcolname == NULL))
	{
		log_error("NULL argv or azColName in callback\n");
		return -1;
	}

	for(i=0; i<argc; i++)
	{
		log_info("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "NULL");
	}
	return 0;
}


int database_init(void)     
{
	int			rv = -1;

	rv = sqlite3_open(DB_NAME, &s_serdb);
	if ( rv != SQLITE_OK )
	{
		log_error("open database[%s] error!\n", DB_NAME);
		return -1;
	}

	log_info("open database[%s] successfully!\n", DB_NAME);

	return 0;
}

/*关闭数据库并记录一条警告*/
void database_term(void)
{
	log_warn("close sqlite database now\n");
	sqlite3_close(s_serdb);

	return ;
}


/*插入数据*/
int database_insert(data_t *data)
{
	char			buf[256];
	char 			*errmsg = 0;

	memset(buf, 0, sizeof(buf));
	snprintf(buf, sizeof(buf), "INSERT INTO %s(SN, DATIME, TEMPERATURE)VALUES('%s', '%s', '%f');", TABLENAME, data->sn, data->time, data->temper);

	log_info("buf:%s\n", buf);

	if(SQLITE_OK != sqlite3_exec(s_serdb, buf, callback, 0, &errmsg))
	{
		log_error("insert table error:%s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	else
	{
		log_info("insert table successfully\n");
		return 0;
	}
}


// 插入前检查唯一性
int database_safe_insert(data_t *data) 
{
	sqlite3_stmt   *stmt;
	int            exists = 0;
	char           *check_sql = "SELECT 1 FROM `client_table` WHERE SN=? AND DATIME=?";

	if ( !data )
	{
		log_error("Invalid input pointers\n");
		return -1;
	}

	if(sqlite3_prepare_v2(s_serdb, check_sql, -1, &stmt, NULL) != SQLITE_OK) 
	{
		log_error("Failed to prepare check statement\n");
		return -1;
	}

	if(sqlite3_bind_text(stmt, 1, data->sn, -1, SQLITE_STATIC) != SQLITE_OK)   /*预处理语句 */
	{
		log_error("Failed to bind sn.\n");
		sqlite3_finalize(stmt);
		return -1;
	}

	if(sqlite3_bind_text(stmt, 2, data->time, -1, SQLITE_STATIC) != SQLITE_OK)
	{
		log_error("Failed to bind datime\n");
		sqlite3_finalize(stmt);
		return -1;
	}

	if (sqlite3_step(stmt) == SQLITE_ROW) 
	{
		exists = 1;
	}
	sqlite3_finalize(stmt);

	if (!exists) 
	{
		return database_insert(data);
	}
	else
	{
		log_warn("Duplicate record, skip insertion\n");
		return 1;
	}
}

int database_table(char *table_name)
{
	char                    query[512];
	int                     rv = -1;
	char                    *sqlError = NULL;

	sprintf(query, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%s';", table_name);
	rv = sqlite3_exec(s_serdb, query, table_callback, NULL, &sqlError);
	if ( rv != SQLITE_OK )
	{
		log_warn("Failed to query the table: %s\n", sqlError);
		goto Cleanup;
	}

	if ( table_exist == 0 )     
	{
		sprintf(query, "CREATE TABLE [%s] (ID CHAR(10), TIME CHAR(10), TEMPERATURE CHAR(10));", table_name);
		rv = sqlite3_exec(s_serdb, query, NULL, NULL, &sqlError);
		if ( rv != SQLITE_OK )
		{
			log_error("Failed to create the table: %s\n", sqlError);
			goto Cleanup;
		}
		log_info("Create table successfully!\n");
	}
	return 0;

Cleanup:
	sqlite3_free(sqlError);
	database_term();
	return -1;
}

static int table_callback(void *exec_para, int n_column, char **column_value, char **column_name)
{
	if ( 1 == n_column )
	{
		table_exist = atoi(*(column_value));
	}

	return 0;
}

